/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;

import java.sql.PreparedStatement;
import controllers.entity.CanBo;
import controllers.entity.Children;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author hoangnv
 */
public class CanBoDAO {
    public static PreparedStatement prst;
    
    public  List <CanBo> getDoanVien(){
        List <CanBo> list  = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        ResultSet result = null;
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT  * FROM canbo WHERE doanvien = 1");
            
            result = prst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (result.next()) {
                int maCanBo = result.getInt("MaCanBo");
                String hoTen = result.getString("HoTen");
                Date ngaySinh = result.getDate("NgaySinh");
                String gioiTinh = result.getString("GioiTinh");
                String chucDanh = result.getString("ChucDanh");
                String chucVu   = result.getString("ChucVu");
                float  heSoLuong = result.getFloat("HeSoLuong");
                float phuCapChucVu  = result.getFloat("PhuCapChucVu");
                float phuCapGiangDay = result.getFloat("PhuCapGiangDay");
                String maSoThue = result.getString("MaSoThue");
                String sotaikhoan=result.getString("SoTaiKhoan");
                String diaChi   = result.getString("DiaChi");
                String soDienThoai = result.getString("SoDienThoai");
                String email    = result.getString("Email");
                boolean dangVien = result.getBoolean("DangVien");
                boolean doanVien = result.getBoolean("DoanVien");
                boolean congDoanVien = result.getBoolean("CongDoanVien");
                String thanhTich    = result.getString("ThanhTich");
                String thongTinKhac = result.getString("ThongTinKhac");
                String khoavien=result.getString("KhoaVien");
                
                CanBo temp =new CanBo(maCanBo,hoTen,ngaySinh,gioiTinh,chucDanh, chucVu, heSoLuong,
             phuCapChucVu,  phuCapGiangDay, maSoThue,sotaikhoan,  diaChi, soDienThoai, email,
             dangVien,doanVien, congDoanVien, thanhTich, thongTinKhac,khoavien);
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public  List <CanBo> getSearchCanBo(String hoten,String chucdanh, String chucvu, String gioitinh, int dangvien1,int dangvien2,int doanvien1,int doanvien2,int congdoan1,int congdoan2){
        List <CanBo> list  = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        ResultSet result = null;
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT  * FROM canbo WHERE "
                    + "hoten LIKE ? and gioitinh LIKE ? and chucdanh LIKE ? and chucvu LIKE ? "
                    + "and ( dangvien = ? or dangvien = ?) and (doanvien = ? or doanvien = ?) and (congdoanvien=? or congdoanvien=?)");
            prst.setString(1, "%" + hoten + "%");
            prst.setString(2, "%" + gioitinh + "%");
            prst.setString(3, "%" + chucdanh + "%");
            prst.setString(4, "%" + chucvu + "%");
            prst.setInt(5,dangvien1 );
            prst.setInt(6,dangvien2);
            prst.setInt(7,doanvien1);
            prst.setInt(8,doanvien2);
            prst.setInt(9,congdoan1);
            prst.setInt(10,congdoan2);
            result = prst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (result.next()) {
                int maCanBo = result.getInt("MaCanBo");
                String hoTen = result.getString("HoTen");
                Date ngaySinh = result.getDate("NgaySinh");
                String gioiTinh = result.getString("GioiTinh");
                String chucDanh = result.getString("ChucDanh");
                String chucVu   = result.getString("ChucVu");
                float  heSoLuong = result.getFloat("HeSoLuong");
                float phuCapChucVu  = result.getFloat("PhuCapChucVu");
                float phuCapGiangDay = result.getFloat("PhuCapGiangDay");
                String maSoThue = result.getString("MaSoThue");
                String sotaikhoan=result.getString("SoTaiKhoan");
                String diaChi   = result.getString("DiaChi");
                String soDienThoai = result.getString("SoDienThoai");
                String email    = result.getString("Email");
                boolean dangVien = result.getBoolean("DangVien");
                boolean doanVien = result.getBoolean("DoanVien");
                boolean congDoanVien = result.getBoolean("CongDoanVien");
                String thanhTich    = result.getString("ThanhTich");
                String thongTinKhac = result.getString("ThongTinKhac");
                String khoavien=result.getString("KhoaVien");
                
                CanBo temp =new CanBo(maCanBo,hoTen,ngaySinh,gioiTinh,chucDanh, chucVu, heSoLuong,
             phuCapChucVu,  phuCapGiangDay, maSoThue, sotaikhoan, diaChi, soDienThoai, email,
             dangVien,doanVien, congDoanVien, thanhTich, thongTinKhac,khoavien);
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public static List <CanBo> getAllCanBo(){
        List <CanBo> list  = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        ResultSet result = null;
         try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT  * FROM CanBo");
            result = prst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (result.next()) {
                int maCanBo = result.getInt("MaCanBo");
                String hoTen = result.getString("HoTen");
                Date ngaySinh = result.getDate("NgaySinh");
                String gioiTinh = result.getString("GioiTinh");
                String chucDanh = result.getString("ChucDanh");
                String chucVu   = result.getString("ChucVu");
                float  heSoLuong = result.getFloat("HeSoLuong");
                float phuCapChucVu  = result.getFloat("PhuCapChucVu");
                float phuCapGiangDay = result.getFloat("PhuCapGiangDay");
                String maSoThue = result.getString("MaSoThue"); 
                String sotaikhoan=result.getString("SoTaiKhoan");
                String diaChi   = result.getString("DiaChi");
                String soDienThoai = result.getString("SoDienThoai");
                String email    = result.getString("Email");
                boolean dangVien = result.getBoolean("DangVien");
                boolean doanVien = result.getBoolean("DoanVien");
                boolean congDoanVien = result.getBoolean("CongDoanVien");
                String thanhTich    = result.getString("ThanhTich");
                String thongTinKhac = result.getString("ThongTinKhac");
                String khoavien=result.getString("KhoaVien");
                
                CanBo temp =new CanBo(maCanBo,hoTen,ngaySinh,gioiTinh,chucDanh, chucVu, heSoLuong,
             phuCapChucVu,  phuCapGiangDay, maSoThue,sotaikhoan, diaChi, soDienThoai, email,
             dangVien,doanVien, congDoanVien, thanhTich, thongTinKhac,khoavien);
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public int getCountCanBo(){
        int count = 0;
        List <CanBo> list = getAllCanBo();
        for (CanBo cb : list){
            int i = cb.getMaCanBo();
            if (i > count ) count = i;
        }
        return count;
    }
    public boolean InsertCanBo(CanBo X){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       } 
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("INSERT INTO "
                    + "CanBo(MaCanBo,HoTen,NgaySinh,GioiTinh,"
                    + "ChucDanh, ChucVu, HeSoLuong,PhuCapChucVu,PhuCapGiangDay, MaSoThue,SoTaiKhoan,DiaChi, SoDienThoai,"
                    + " Email,DangVien,DoanVien, CongDoanVien, ThanhTich, ThongTinKhac,KhoaVien) "
                    + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            prst.setInt(1, X.getMaCanBo());
            prst.setString(2,X.getHoTen());
            prst.setDate(3,new java.sql.Date((X.getNgaySinh()).getTime()));
            prst.setString(4,X.getGioiTinh());
            prst.setString(5,X.getChucDanh());
            prst.setString(6,X.getChucVu());
            prst.setFloat(7, X.getHeSoLuong());
            prst.setFloat(8,X.getPhuCapChucVu());
            prst.setFloat(9,X.getPhuCapGiangDay());
            prst.setString(10,X.getMaSoThue());
            prst.setString(11,X.getSoTaiKhoan());
            prst.setString(12,X.getDiaChi());
            prst.setString(13,X.getSoDienThoai());
            prst.setString(14,X.getEmail());
            prst.setBoolean(15,X.getDangVien());
            prst.setBoolean(16,X.getDoanVien());
            prst.setBoolean(17,X.getCongDoanVien());
            prst.setString(18,X.getThanhTich());
            prst.setString(19,X.getThongTinKhac());
            prst.setString(20,X.getKhoaVien());
            
            if (prst.executeUpdate()> 0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean UpdateCanBo(CanBo X, int MaCanBo){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("UPDATE CanBo SET "
                    + "MaCanBo = ?, HoTen = ?, NgaySinh = ?,GioiTinh = ?,"
                    + "ChucDanh = ?,ChucVu = ?, HeSoLuong = ?, PhuCapChucVu = ?,"
                    + " PhuCapGiangDay = ?, MaSoThue = ?,SoTaiKhoan=?,DiaChi = ?,"
                    + "SoDienThoai = ?, Email = ?, DangVien = ?, DoanVien = ?,"
                    + "CongDoanVien = ?, ThanhTich = ?,ThongTinKhac = ?,KhoaVien=? "
                    + "WHERE MaCanBo = ? ");
            prst.setInt(1, X.getMaCanBo());
            prst.setString(2,X.getHoTen());
            prst.setDate(3,new java.sql.Date((X.getNgaySinh()).getTime()));
            prst.setString(4,X.getGioiTinh());
            prst.setString(5,X.getChucDanh());
            prst.setString(6,X.getChucVu());
            prst.setFloat(7, X.getHeSoLuong());
            prst.setFloat(8,X.getPhuCapChucVu());
            prst.setFloat(9,X.getPhuCapGiangDay());
            prst.setString(10,X.getMaSoThue());
            prst.setString(11,X.getSoTaiKhoan());
            prst.setString(12,X.getDiaChi());
            prst.setString(13,X.getSoDienThoai());
            prst.setString(14,X.getEmail());
            prst.setBoolean(15,X.getDangVien());
            prst.setBoolean(16,X.getDoanVien());
            prst.setBoolean(17,X.getCongDoanVien());
            prst.setString(18,X.getThanhTich());
            prst.setString(19,X.getThongTinKhac());
            prst.setString(20,X.getKhoaVien());
            prst.setInt(21,MaCanBo);
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean DeleteCanBo(int MaCanBo){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("DELETE FROM CanBo WHERE MaCanBo = ? ");
            prst.setInt(1,MaCanBo);
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    
}
